<?php
namespace Unicom\Controller;
use Think\Controller;

class ReportController extends AdminController {

	static $map = array('WS' => 'SJDR', 'WD' => 'WTD', 'WV' => 'fsdf');

	static $table1 = array('id', 'monum', 'mocontent', 'mophone', 'motime', 'linkid', 'create_time');
	static $table2 = array('id', 'linkid', 'phone', 'servcode', 'payfee', 'paytime', 'status', 'create_time');
	static $table3 = array('id', 'phone', 'servnum', 'servcode', 'payfee', 'paytime', 'status', 'create_time');

	public function index() {

		layout(false);
		$REQUEST = (array) I('request.');
		$date_time = I('param.date_time', date('Y-m-d'), 'string');
		$date_end = I('param.date_end', date('Y-m-d'), 'string');

		import('ORG.Util.Page'); // 导入分页类

		$model = M('cp.verify_phone', 'cp_');
		$where = $date_time ? "where date_format(v.create_time,'%Y-%m-%d') between '" . $date_time . "'" . " and '" . $date_end . "'" : "";
		$count = $model->query("select count(*) as count from (select *,count(*) as count,date_format(v.create_time,'%Y-%m-%d') as date from cp.cp_order_log as v " . $where . " group by v.mocontent, date_format(v.create_time,'%Y-%m-%d') order by date desc) as s")[0]['count']; // 查询满足要求的总记录数
		$listRows = 15;
		if (isset($REQUEST['r'])) {
			$listRows = (int) $REQUEST['r'];
		} else {
			$listRows = C('LIST_ROWS') > 0 ? C('LIST_ROWS') : 10;
		}
		$Page = new \Think\Page($count, $listRows); // 实例化分页类 传入总记录数和每页显示的记录数
		$show = $Page->show(); // 分页显示输出

		$sql = "select *,servcode as mocontent,count(*) as count,date_format(v.create_time,'%Y-%m-%d') as date from cp.cp_order_log as v " . $where . " group by v.servcode, date_format(v.create_time,'%Y-%m-%d') order by date desc limit " . $Page->firstRow . ',' . $Page->listRows;
		$data = $model->query($sql);
		foreach ($data as $key => &$value) {
                $value['sync_ok'] = $model->query("select count(*) as sync_ok from cp.cp_order_log where sync=0 and status=0 and servcode='{$value['servcode']}' and date_format(create_time,'%Y-%m-%d')=" . "'" . $value['date'] . "'")[0]['sync_ok'];
                $value['succ_ok'] = $model->query("select count(*) as succ_ok from cp.cp_order_log where servcode='{$value['servcode']}' and status=0 AND  date_format(create_time,'%Y-%m-%d')=" . "'" . $value['date'] . "'")[0]['succ_ok'];


		}

		$this->assign('list', $data);
		$this->assign('page', $show);
		$this->assign('now', $date_time ? $date_time : date("Y-m-d"));
		$this->assign('date_end', $date_end ? $date_end : date("Y-m-d"));
		$this->assign('pagesize', $REQUEST['r'] ? $REQUEST['r'] : 10);
		$this->display();
	}

	/**
	 * 根据手机号查找记录
	 * @return [type] [description]
	 */
	public function search() {
		layout(false);
		if (IS_GET) {
			$this->display();
		} else {
			$phone = I('param.phone', '', 'string');
			$model = M('cp.verify_phone', 'cp_');
			$sql = "select v.mophone,v.mocontent,v.monum,v.linkid,v.motime,v.create_time,if(p.payfee,p.payfee,o.payfee) as payfee,if(p.paytime,p.paytime,o.paytime) as paytime,if(p.status,p.status,o.status) as status   from cp.cp_verify_phone as v left join cp.cp_payment_log as p on v.linkid=p.linkid left join cp.cp_order_log as o on v.linkid=o.linkid where v.mophone='" . $phone . "'" . " order by v.create_time desc";
			$table1 = "select mophone,mocontent,monum,linkid,motime,create_time,1 as src from cp.cp_verify_phone where mophone='" . $phone . "'";
			$table2 = "select phone as mophone,servnum,linkid,create_time,payfee,paytime,status,2 as src from cp.cp_payment_log where phone='" . $phone . "'";
			$table3 = "select phone as mophone,servcode as mocontent,servnum,linkid,create_time,payfee,paytime,status,3 as src from cp.cp_order_log where phone='" . $phone . "'";

			$data1 = $model->query($table1);
			$data2 = $model->query($table2);
			$data3 = $model->query($table3);
			$list = array_merge($data1, $data2, $data3);
			$this->assign("list", $list);
			$this->display();
			//var_dump($data1);
		}

	}

	//导出数据表
	public function exportTable() {
		set_time_limit(100000000);
		$date_time = I('param.date_time', '', 'string');
		$date_end = I('param.date_end', '', 'string');
		$type = I('param.type', 0, 'int');

		$table_name = "cp_verify_phone";
		$headers = array();
		switch ($type) {
		case 0:
			$table_name = "cp_verify_phone ";
			$headers = $this::$table1;
			break;
		case 1:
			$table_name = "cp_payment_log ";
			$headers = $this::$table2;
			break;
		case 2:
			$table_name = "cp_order_log ";
			$headers = $this::$table3;
			break;

		default:
			# code...
			break;
		}
		$where = $date_time ? "where DATE_FORMAT( create_time, '%Y-%m-%d' ) between '" . $date_time . "'" . "and '" . $date_end . "'" : "";
		$sql = "SELECT * FROM " . $table_name . $where . " order by create_time desc";
		$model = M('cp.verify_phone', 'cp_');
		$data = $model->query($sql);

		if ($data) {
			$this->exportexcel($data, $headers, $table_name . "_{$date_time}");
		} else {
			echo "没有数据";
		}
		//var_dump($data);

	}

	/**
	 * 没用
	 * @param  [type] $fileName [description]
	 * @param  [type] $headArr  [description]
	 * @param  [type] $data     [description]
	 * @return [type]           [description]
	 */
	private function getExcel($fileName, $headArr, $data) {
		//导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
		import("Org.PHPExcel");
		import("Org.PHPExcel.Writer.Excel5");
		import("Org.PHPExcel.IOFactory.php");

		$date = date("Y_m_d", time());
		//$fileName .= "_{$date}.xls";

		//创建PHPExcel对象，注意，不能少了\
		$objPHPExcel = new \PHPExcel();
		$objProps = $objPHPExcel->getProperties();

		//设置表头
		$key = ord("A");
		//print_r($headArr);exit;
		foreach ($headArr as $v) {
			$colum = chr($key);
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);

			$key += 1;
		}

		$column = 2;
		$objActSheet = $objPHPExcel->getActiveSheet();

		//print_r($data);exit;
		foreach ($data as $key => $rows) {
			//行写入
			$span = ord("A");
			foreach ($rows as $keyName => $value) {
// 列写入
				$j = chr($span);
				$objActSheet->setCellValue($j . $column, $value);
				$span++;
			}
			$column++;
		}

		//$fileName = iconv("utf-8", "gb2312", $fileName);

		//重命名表
		//$objPHPExcel->getActiveSheet()->setTitle('test');
		//设置活动单指数到第一个表,所以Excel打开这是第一个表
		$objPHPExcel->setActiveSheetIndex(0);
		ob_end_clean(); //清除缓冲区,避免乱码
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Type: application/octet-stream');
		header("Content-Disposition: attachment;filename=\"$fileName\"");
		header('Cache-Control: max-age=0');

		$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		$objWriter->save('php://output'); //文件通过浏览器下载
		exit;
	}

/**
 * 导出数据为excel表格
 *@param $data    一个二维数组,结构如同从数据库查出来的数组
 *@param $title   excel的第一行标题,一个数组,如果为空则没有标题
 *@param $filename 下载的文件名
 *@examlpe
 *$stu = M ('User');
 *$arr = $stu -> select();
 *exportexcel($arr,array('id','账户','密码','昵称'),'文件名!');
 */
	private function exportexcel($data = array(), $title = array(), $filename = 'report') {
		header("Content-type:application/octet-stream");
		header("Accept-Ranges:bytes");
		header("Content-type:application/vnd.ms-excel");
		header("Content-Disposition:attachment;filename=" . $filename . ".xls");
		header("Pragma: no-cache");
		header("Expires: 0");
		//导出xls 开始
		if (!empty($title)) {
			foreach ($title as $k => $v) {
				$title[$k] = iconv("UTF-8", "GB2312", $v);
			}
			$title = implode("\t", $title);
			echo "$title\n";
		}
		if (!empty($data)) {
			foreach ($data as $key => $val) {
				foreach ($val as $ck => $cv) {
					$data[$key][$ck] = iconv("UTF-8", "GB2312", $cv);
				}
				$data[$key] = implode("\t", $data[$key]);

			}
			echo implode("\n", $data);
		}
	}

	/**
	 * 周报表list
	 * @return [type] [description]
	 */
	public function payment() {
		layout(false);
		$model = M('cp.order_log', 'cp_');
		$sql = "select s.week,s.servcode,s.price,s.count,s.count*7 as total_price,if(a.id,'已经结算','未结算') as payment  from (SELECT date_format(create_time,'%u') as week,count(*) as count,servcode,7 as price  FROM cp.cp_order_log WHERE  sync=0 and status=0 and servcode='WTD' group by week order by week desc) as s left join cp.cp_accounts as a on s.week=a.week ";
		$data = $model->query($sql);

		$this->assign("list", $data);

		$this->display();
		// var_dump($data);

	}

	/**
	 * 单周支付报表
	 * @param  [type] $week [description]
	 * @return [type]       [description]
	 */
	public function paymentWeek($week) {
		layout(false);
		$model = M('cp.order_log', 'cp_');
		$sql = "SELECT date_format(create_time,'%Y-%m-%d') as create_time,count(*) as count,servcode,7 as price FROM cp.cp_order_log WHERE date_format(create_time,'%u')=" . $week . " and sync=0 and servcode='WTD' and status=0  group by date_format(create_time,'%Y-%m-%d') order by create_time desc";
		$data = $model->query($sql);
		$total = 0;
		foreach ($data as $key => $value) {
			$total += $value['count'];
		}
		$total_price = $total * 7;
		$this->assign("list", $data);
		$this->assign("total", array('total_count' => $total, 'total_price' => $total_price));
		$this->assign("week", $week);
		$this->display();

	}

	/**
	 * 支付确认
	 * @return [type] [description]
	 */
	public function paymentEnter() {
		$week = I('param.week', 0, 'int');
		$payfee = I('param.payfee', 0, 'int');
		$count = I('param.count', 0, 'int');
		$model = M('cp.order_log', 'cp_');
		//$sql ="update  cp.cp_order_log set payment=1 WHERE date_format(create_time,'%u')=".$week." and sync=0 and payment=0";
		$sql = "select * from  cp.cp_order_log  WHERE date_format(create_time,'%u')=" . $week . " and sync=0 and payment=1";
		$model->startTrans();
		$sucess_num = $model->where("date_format(create_time,'%u')=" . $week . " and sync=0 and servcode='WTD' and payment=0")->setField('payment', 1);
		$data = 0;
		if ($sucess_num) {

			$data = M('cp.accounts', 'cp_')->add(array('week' => $week, 'payfee' => $payfee, 'count' => $count, 'create_time' => time()));
			if ($data) {
				$model->commit();
				$this->success("结算成功", '', true);
			} else {
				$model->rollback();
				$this->error("结算失败", '', true);
			}
		} else {
			$this->error("已经结算过了", '', true);
		}

	}

}